import sqlite3
import xlrd
import xlwt



con = sqlite3.connect('cov19.db')
cur = con.cursor()


def change_date(excel_date):
    try:
        t = int(excel_date)
        tmp = (t-70*365-19)*86400 - 8*3600
    except:
        tmp = 162544000
    return tmp


filename = r'E:\休宁\cov\cimi\cimi.xls'
sheets_name = 4
# filename = r'E:\休宁\cov\relation.xlsx'
# sheets_name = 5
workbook = xlrd.open_workbook(filename)
sheet = workbook.sheets()[sheets_name]
rows = sheet.nrows
#
# cur.execute('select name,uuid from person')
# names_rslt = cur.fetchall()
#
sql_relation = '''insert into relation values (
    null,"{}","{}",{},"密接接触",null,"{}",null
)
'''
#
# # 次密关系图
#
# for i in range(1,10):
#     tmp = [str(sheet.cell(i,j).value) for j in range(5)]
#     for i in names_rslt:
#         if i[0] in tmp[2]:
#             tmp2 = [tmp[1],i[1],tmp[3],tmp[4]]
#             cur.execute('select * from relation where uuid = "{}" and contact_uuid = "{}"'.format(tmp[1],i[1]))
#             dup_rslt = cur.fetchall()
#             if not dup_rslt:
#                 sql_relation2 = sql_relation.format(*tmp2)
#                 cur.execute(sql_relation2)
#
# # con.commit()
# #

# 密接关系图


for i in range(1,rows):
    tmp = [str(sheet.cell(i,j).value) for j in range(5)]
    sql = '''insert into abc values ("{}","{}","{}","{}","{}")
        '''.format(*tmp)
    cur.execute(sql)
con.commit()

cur.execute('select * from abc')
mijie_rslt = cur.fetchall()
# k = 0
# for i in mijie_rslt:
#     if ',' in i[2]:
#         names = i[2].split(',')
#         for name in names:
#             cur.execute('select uuid from person where name = "{}"'.format(name))
#             uuid_rslt = cur.fetchone()
#             if uuid_rslt:
#                 uuid = uuid_rslt[0]
#                 cur.execute('select * from relation where uuid = "{}" and contact_uuid = "{}"'.format(i[1],uuid))
#                 dup_rslt = cur.fetchall()
#                 if not dup_rslt:
#                     k += 1
#                     print(i)
#                     sql_relation2 = sql_relation.format(str(i[1]).upper(),uuid.upper(),change_date(i[3]),i[4])
#                     cur.execute(sql_relation2)
#     else:
#         cur.execute('select uuid from person where name = "{}"'.format(i[2]))
#         uuid_rslt = cur.fetchone()
#         if uuid_rslt:
#             uuid = uuid_rslt[0]
#             cur.execute('select * from relation where uuid = "{}" and contact_uuid = "{}"'.format(i[1],uuid))
#             dup_rslt = cur.fetchall()
#             if not dup_rslt:
#                 k += 1
#                 print(i)
#                 sql_relation2 = sql_relation.format(str(i[1]).upper(),uuid.upper(),change_date(i[3]),i[4])
#                 cur.execute(sql_relation2)

miss_rslt = []
for i in mijie_rslt:
    if ',' in i[2]:
        names = i[2].split(',')
        for name in names:
            cur.execute('select uuid from person where name = "{}"'.format(name))
            uuid_rslt = cur.fetchone()
            if not uuid_rslt:
                miss_rslt.append(name)
    else:
        cur.execute('select uuid from person where name = "{}"'.format(i[2]))
        uuid_rslt = cur.fetchone()
        if not uuid_rslt:
            miss_rslt.append(i[2])
print(miss_rslt)
# con.commit()
# cur.execute('delete from abc')
